There have been provided various types of databases, and in such databases, a database called a relational database is currently most common. Performance of this relational database can be upgraded by reducing a response time to cumulatively increasing amounts of data and/or the number of users. The upgraded performance has an influence on quality of its overall performance, thereby being an important subject in business.
To upgrade the performance of a relational database, many tuning apparatuses and methods have been proposed for analyzing and/or improving the performance (hereinafter, in the description, the term “tuning” is defined as a scheme to analyze and/or improve the performance of the relational database). A relatively simple tuning scheme is, for example, to improve hardware resources by mounting sophisticated CPUs and/or increasing a memory capacity or to optimize database objects. However, this tuning scheme cannot provide an effective improvement in cases where a large number of accesses are generated inefficiently and thus a large amount of useless data is present in cache memory due, to that fact that proper indexes are not applied to access to a large-scale table. With this consideration, as a tuning scheme which improves effectively the performance, tuning a query language has become mainstream.
The query language is, here, a language used for operation of the relational database, such as creation or deletion of databases or registration or search of information, and a typical example is SQL (Structured Query Language). In the present specification, the query language will be hereinafter explained, by way of example, by SQL as long as there is no specific definition.
By the way, when databases are smaller in their sizes and the tuning of the databases are carried out for every SQL command, differences of skill levels of administrators do not tend to appear clearly in the tuning achievements. However, if the database is large-scale, the technique of analyzing the database for each SQL command is dependent on individual skills which require considerable experience. In addition, even if the administrator is well experienced, searching a SQL command which has caused a bottleneck is a large amount of load on the administrator, and this search is not efficient.
Moreover, in the tuning performed with each SQL command, it becomes necessary to optimally add an index to a searched SQL command which has caused the bottleneck. This addition of the index is also dependent on the administrator's skills, thus it is difficult to expect consistent improvement in the tuning.
To solve the foregoing problem concerning the analysis performed every SQL command, there is proposed a database tuning apparatus (for example, refer to PTL1). In this apparatus, for instance, statistical information of SQL commands executed on the database is collectively displayed for each transaction so that the utilization status of each SQL command can be analyzed every transaction. The configurations of this apparatus make it possible to remove SQL-overlapped parts, distribute a SQL command whose usage frequency is extremely high, into a group of other SQL commands, interchange the orders of the SQL commands, and execute other processes, resulting in relatively easier tuning operations compared with the analysis technique performed for every SQL command.
Further, another system is provided as an automatic generation and addition system for indexes (refer to PTL2). In this system, operating information of all issued SQL commands is acquired as information showing change history, and based on the stored operating information, a total execution time is obtained by collecting an execution time for each of the same type of SQL commands whose process contents are consistent with each other. A new index is then added to columns which are objects of SQL commands included in an upper half group of the total execution times, and concurrently, these processes are repeated until an accumulated amount of the total execution time for each SQL command becomes a minimum value. According to this configuration, regardless of factors including individual skills of system administrators, optimum indexes can be generated and added automatically such that there is brought a balance between efficiencies of search and update under a designated trial condition, improving a total operating efficiency of the RDB.